# -*- coding:utf-8 -*—
import os
import time

import pandas as pd
from pandas import DataFrame


def readExcel(excelPath):
    df: DataFrame = pd.read_excel(io=excelPath)
    return df

def exportExcel(open_path,df):
    filename = os.path.basename(open_path)
    # print(os.path.basename(path))

    stem, suffix = os.path.splitext(filename)
    # print(stem)  # test
    # print(suffix)  # .py
    exportPath = os.path.split(os.path.abspath(open_path))[0]
    filename = os.path.split(os.path.abspath(open_path))[1]
    now_time = time.strftime('%Y-%m-%d-%H-%M-%S', time.localtime())
    # outname = "./"+stem+"更新excel列" + now_time + ".xlsx"
    outname = "./"+stem+"更新excel列" + now_time +  ".xlsx"
    # print(outname)
    outputPath = exportPath +outname
    # outputPath = exportPath +'./excel转txt1.txt'
    # df.to_excel(outputPath, sep='\t', index=None)
    df.to_excel(outputPath,index = False,encoding="utf-8")
    print('导出excel完成')

def updateExcel(sourceExcelPath, tartgetExcelPath, sourceIndex, targetIndex, sourceValueColoumn, targetUpdateColoumn):
    ''':argument
    sourceExcelPath:源excel路径，如点表
    tartgetExcelPath：目标excel路径，如线表
    sourceIndex：源excel的对应列，如plptno
   targetIndex：目标excel的对应列，如plpt0，PLPT1
    sourceValueColoumn：源excel数据列，如grid
    targetUpdateColoumn：目标excel的待更新数据列，如plpt0ht，plpt1ht'''
    dfsources = readExcel(sourceExcelPath)
    dftarget = readExcel(tartgetExcelPath)
    for targetIndexItem in dftarget[targetIndex].tolist():
        if dfsources[dfsources[sourceIndex] == targetIndexItem][sourceValueColoumn].tolist().__len__()>0:
            dftarget.loc[dftarget[targetIndex] == targetIndexItem, [targetUpdateColoumn]] = dfsources[dfsources[sourceIndex] == targetIndexItem][sourceValueColoumn].tolist()[0]
        else:
            print("源数据没有找到目标值" ,targetIndexItem)
    # dftarget.to_csv()
    exportExcel(tartgetExcelPath,dftarget)
def updateExcel2(sourceExcelPath, tartgetExcelPath, sourceIndex, targetIndex, sourceValueColoumn, targetUpdateColoumn,sourceValueColoumn2, targetUpdateColoumn2):
    ''':argument
    sourceExcelPath:源excel路径，如点表
    tartgetExcelPath：目标excel路径，如线表
    sourceIndex：源excel的对应列，如plptno
   targetIndex：目标excel的对应列，如plpt0，PLPT1
    sourceValueColoumn：源excel数据列，如grid
    targetUpdateColoumn：目标excel的待更新数据列，如plpt0ht，plpt1ht'''
    dfsources = readExcel(sourceExcelPath)
    dftarget = readExcel(tartgetExcelPath)
    for targetIndexItem in dftarget[targetIndex].tolist():
        if dfsources[dfsources[sourceIndex] == targetIndexItem][sourceValueColoumn].tolist().__len__()>0 and dfsources[dfsources[sourceIndex] == targetIndexItem][sourceValueColoumn2].tolist().__len__()>0:
            dftarget.loc[dftarget[targetIndex] == targetIndexItem, [targetUpdateColoumn]] = dfsources[dfsources[sourceIndex] == targetIndexItem][sourceValueColoumn].tolist()[0]
            dftarget.loc[dftarget[targetIndex] == targetIndexItem, [targetUpdateColoumn2]] = dfsources[dfsources[sourceIndex] == targetIndexItem][sourceValueColoumn2].tolist()[0]
        else:
            print("源数据没有找到目标值" ,targetIndexItem)
    # dftarget.to_csv()
    exportExcel(tartgetExcelPath,dftarget)


if __name__ == '__main__':
    # sourcePath = r"E:\项目相关\青羊区项目\青羊设计数据问题\一期提取点\2.管点导入\v1-管点-合并excel2023-04-06-17-25-46去重结果2023-04-06-17-31-10转换坐标2023-04-06-21-39-40.xlsx"
    # sourceIndex = 'EXP_NO'
    # sourceValueColoumn = 'SUR_H'
    #
    # # targetPath = r"F:\data\chengdu\2022.09.23导出模型\一期设计数据\2022.11.02合并结果\线表-合并excel2022-11-02-09-16-26.xlsx"
    # # targetIndex = '起点编号'
    # # targetUpdateColoumn = '起点高赋值'
    #
    # targetPath = r"E:\项目相关\青羊区项目\青羊设计数据问题\一期提取点\2.管线导入\v1-合并excel2023-04-06-17-21-36更新excel列2023-04-07-14-19-59.xlsx"
    # targetIndex = '终点编号'
    # targetUpdateColoumn = '终点高赋值'
    # updateExcel(sourcePath, targetPath, sourceIndex, targetIndex, sourceValueColoumn, targetUpdateColoumn)
    # print('----完成---')

    sourcePath = r"E:\data\网络下载数据\221007 行政区划、POI、道路等矢量数据\2020年中国行政村级区划代码及经纬度\2020年村级及以上各级行政区区划代码及经纬度.xlsx"
    sourceIndex = '乡镇级'
    sourceValueColoumn = 'lng_84'
    sourceValueColoumn2 = 'lat_84'

    # targetPath = r"F:\data\chengdu\2022.09.23导出模型\一期设计数据\2022.11.02合并结果\线表-合并excel2022-11-02-09-16-26.xlsx"
    # targetIndex = '起点编号'
    # targetUpdateColoumn = '起点高赋值'

    targetPath = r"E:\data\网络下载数据\221007 行政区划、POI、道路等矢量数据\2020年中国行政村级区划代码及经纬度\sys_area_空白2.xlsx"
    targetIndex = 'remark'
    targetUpdateColoumn = 'lng'
    targetUpdateColoumn2 = 'lat'
    updateExcel2(sourcePath, targetPath, sourceIndex, targetIndex, sourceValueColoumn, targetUpdateColoumn,sourceValueColoumn2, targetUpdateColoumn2)
    print('----完成---')